﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Threading;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using System.IO;
using System.Xml;
using System.Drawing;
using System.Data;
using PdfLib.TextScraping;

namespace Workflows.Components.TextMining.EnumerationObjects
{
    /// <summary>
    /// 
    /// </summary>
    public class TableDataExtractorWorker:IWorker
    {
        #region fields
        private Thread _WorkerThread;
        private Guid _JobTicketID;
        private TableDataExtractorInstruction _Instruction;
        private TableDataExtractorOutput _Output;
        private RunContext _Context;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        private object _SyncObj;
        private StreamWriter _LogWriter;
        private StreamWriter _ErrorWriter;
        private ManualResetEvent[] _ManualResets;
        private int[] _CurrentWorkloads;

        // input files
        private TableRowDetectorInstruction _RowSetting;
        private Dictionary<int, string> _DocIDFilePathMappings;
        private Dictionary<int, Dictionary<int, Dictionary<string, RectangleF>>> _FileTableLayouts;

        // output
        private int _RecordsExtracted;
        private Dictionary<int, List<string>> _ExtractedResultFiles;
        #endregion

        #region IWorker Members

        /// <summary>
        /// handler to worker thread
        /// </summary>
        public Thread WorkerThread
        {
            get { return _WorkerThread; }
        }

        /// <summary>
        /// allow instruction to be changed at runtime
        /// </summary>
        public InstructionBase Instruction
        {
            get { return _Instruction; }
        }

        /// <summary>
        /// output
        /// </summary>
        public ResultBase Output
        {
            get { return _Output; }
        }

        /// <summary>
        /// allow worker to be created based on component type
        /// </summary>
        public Type ComponentType
        {
            get { return typeof(TableDataExtractorComponent); }
        }

        /// <summary>
        /// runtime context retrieved from appl domain
        /// </summary>
        public RunContext Context
        {
            get { return this._Context; }
            set { this._Context = value; }
        }

        /// <summary>
        /// this must be called before actual work
        /// </summary>
        /// <param name="ticketGuid"></param>
        /// <param name="request"></param>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (TableDataExtractorInstruction)request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        /// <summary>
        /// this is called within subflow, where runtime context is also initialized
        /// </summary>
        /// <param name="context"></param>
        /// <param name="ticketGuid"></param>
        /// <param name="request"></param>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (TableDataExtractorInstruction)request;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        /// <summary>
        /// this is called synchronously, used in testing mode
        /// </summary>
        public void DoWork()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = Thread.CurrentThread;
            this.ExtractTableData();
        }


        /// <summary>
        /// this is called by job dispatcher
        /// </summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = new Thread(new ThreadStart(this.ExtractTableData));
            this._WorkerThread.Start();
        }

        /// <summary>
        /// this is called by job monitor/manager
        /// </summary>
        public void CancelWork()
        {
            if (this._WorkerThread != null && this._WorkerThread.IsAlive)
            {
                this._WorkerThread.Abort();
                if (this.WorkerCancelledEvent != null)
                {
                    this.WorkerCancelledEvent(this._JobTicketID, this.GetType(), this._Instruction);
                }
            }
        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;

        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #endregion

        #region work
        private void ExtractTableData()
        {
            _SyncObj = new object();
            this._ManualResets = new ManualResetEvent[this._Instruction.ThreadPoolSize];
            this._CurrentWorkloads = new int[this._Instruction.ThreadPoolSize];
            this._ExtractedResultFiles = new Dictionary<int, List<string>>();

            try
            {
                string appFolderPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
                appFolderPath = Path.Combine(appFolderPath, "Workflow data");
                if (!Directory.Exists(appFolderPath))
                {
                    Directory.CreateDirectory(appFolderPath);
                }
                // init 
                if (string.IsNullOrEmpty(this._Instruction.LogFilePath))
                {
                    this._Instruction.LogFilePath = Path.Combine(appFolderPath, "TableDataExtract.log");
                }
                if (File.Exists(this._Instruction.LogFilePath))
                {
                    File.Delete(this._Instruction.LogFilePath);
                }
                this._LogWriter = new StreamWriter(this._Instruction.LogFilePath);
                if (string.IsNullOrEmpty(this._Instruction.ErrFilePath))
                {
                    this._Instruction.ErrFilePath = Path.Combine(appFolderPath, "TableDataExtract.err");
                }
                if (File.Exists(this._Instruction.ErrFilePath))
                {
                    File.Delete(this._Instruction.ErrFilePath);
                }
                this._ErrorWriter = new StreamWriter(this._Instruction.ErrFilePath);
                int filesProcessed = 0;
                this._RecordsExtracted = 0;

                // row settings 
                this._RowSetting = new TableRowDetectorInstruction();
                this._RowSetting.RelativeMinRowSpacing = TableDetector.RelativeRowSpacing;
                this._RowSetting.DetectRequiredFields = true;
                this._RowSetting.DetectHyphonWrap = true;

                // layout settings 
                this._FileTableLayouts = this.ReadTableLayout(this._Instruction.TableLayoutXmlFilePath);

                // file list
                this._DocIDFilePathMappings = this.ReadFileList(this._Instruction.FileListXmlFilePath);

                // browse and loop
                if (this._DocIDFilePathMappings != null && this._DocIDFilePathMappings.Count > 0)
                {
                    int[] allDocIDs = new int[this._DocIDFilePathMappings.Count];
                    this._DocIDFilePathMappings.Keys.CopyTo(allDocIDs, 0);
                    int i = 0;
                    while (i < allDocIDs.Length)
                    {
                        List<int> docIDs = new List<int>();
                        for (int workerID = 0; workerID < this._Instruction.ThreadPoolSize; workerID++)
                        {
                            if (i < allDocIDs.Length)
                            {
                                docIDs.Add(allDocIDs[i]);
                                i++;
                            }
                            else
                                break;
                        }
                        this._CurrentWorkloads = docIDs.ToArray();
                        this._ManualResets = new ManualResetEvent[docIDs.Count];
                        for (int k = 0; k < docIDs.Count; k++)
                        {
                            this._ManualResets[k] = new ManualResetEvent(false);
                            ThreadPool.QueueUserWorkItem(new WaitCallback(ExtractTableDataFromFile), k);
                        }
                        WaitHandle.WaitAll(this._ManualResets);

                        filesProcessed = i;
                        if (this.WorkerStatusChangedEvent != null && filesProcessed % 10 == 0)
                        {
                            int percent = filesProcessed * 100 / allDocIDs.Length;
                            string filePath = this._DocIDFilePathMappings[allDocIDs[i - 1]];
                            string msg = string.Format("Extracting table data: {0} of {1}: {2}...", filesProcessed, allDocIDs.Length,
                                                       filePath);
                            this.WorkerStatusChangedEvent(this._JobTicketID, this.GetType(), this._Instruction, this.WorkflowName, this.ComponentName, msg,
                                                          percent);
                        }
                    }
                }

                if(!string.IsNullOrEmpty(this._Instruction.ConnStr))
                {
                    Dictionary<int, PdfScanOutput.PdfScanResult> scanResults =
                        this.ReadTableScanResult(this._Instruction.TableScanXmlFilePath);
                    List<int> allDocIDs = new List<int>();
                    int[] allDocIDArray=new int[this._DocIDFilePathMappings.Count];
                    this._DocIDFilePathMappings.Keys.CopyTo(allDocIDArray, 0);
                    allDocIDs.AddRange(allDocIDArray);
                    this.SaveToDB(allDocIDs, scanResults, this._ExtractedResultFiles);
                }

                this._Output =
                    new TableDataExtractorOutput(
                        this._Instruction.OwnerComponentID, this._JobTicketID,
                        this._Instruction.TableExtractorOutputFolder,
                        filesProcessed, this._RecordsExtracted);

                if (this.WorkerFinishedEvent != null)
                {
                    this.WorkerFinishedEvent(this._JobTicketID, this.GetType(), this._Instruction, this._Output);
                }
            }
            catch (Exception ex)
            {
                if (this.WorkerErredEvent != null)
                {
                    this.WorkerErredEvent(this._JobTicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
            finally
            {
                this._LogWriter.Flush();
                this._LogWriter.Close();
                this._ErrorWriter.Flush();
                this._ErrorWriter.Close();
            }
        }

        private void ExtractTableDataFromFile(object pObject)
        {
            int idx = (int) pObject;
            int docID = this._CurrentWorkloads[idx];
            string filePath = this._DocIDFilePathMappings[docID];
            string fileName = Path.GetFileName(filePath);
            try
            {
                if (this._FileTableLayouts.ContainsKey(docID))
                {
                    Dictionary<int, Dictionary<string, RectangleF>> layoutByPages = this._FileTableLayouts[docID];
                    Dictionary<int, DataTable> tableDataByPage = new Dictionary<int, DataTable>();
                    foreach (int pageNumber in layoutByPages.Keys)
                    {
                        Dictionary<string, RectangleF> colHeaderLayout = layoutByPages[pageNumber];
                        DataTable dtData = TableDetector.ReadTableData(
                            filePath, pageNumber, colHeaderLayout, this._RowSetting);
                        if (dtData != null && dtData.Rows.Count > 0)
                        {
                            tableDataByPage.Add(pageNumber, dtData);

                            lock(this._SyncObj)
                            {
                                this._LogWriter.WriteLine(
                                    string.Format("{0}: {1} extracted {2} rows from page {3}",
                                                  DateTime.Now.ToString(), fileName,
                                                  dtData.Rows.Count, pageNumber));
                                this._LogWriter.Flush();
                            }
                        }
                        else
                        {
                            lock (this._SyncObj)
                            {
                                this._LogWriter.WriteLine(
                                    string.Format("{0}: {1} extracted {2} rows from page {3}",
                                                  DateTime.Now.ToString(), fileName,
                                                  0, pageNumber));
                                this._LogWriter.Flush();
                            }
                        }
                    }
                    lock (this._SyncObj)
                    {
                        this.WriteTableDataToOutput(docID, fileName, tableDataByPage);
                    }
                }
            }
            catch (Exception ex1)
            {
                lock (this._SyncObj)
                {
                    this._ErrorWriter.WriteLine(
                        string.Format("{0}: {1} - error: {2}",
                                      DateTime.Now.ToString(), fileName, ex1.ToString()));
                    this._ErrorWriter.Flush();
                }
            }
            finally
            {
                this._ManualResets[idx].Set();
            }
        }
        #endregion

        #region read input
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableLayoutXmlFilePath"></param>
        /// <returns></returns>
        private Dictionary<int, Dictionary<int,Dictionary<string,RectangleF>>> ReadTableLayout(string tableLayoutXmlFilePath)
        {
            Dictionary<int, Dictionary<int, Dictionary<string, RectangleF>>> layout =
                new Dictionary<int, Dictionary<int, Dictionary<string, RectangleF>>>();
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load(tableLayoutXmlFilePath);
            XmlNodeList fileNodes = xDoc.SelectNodes("//Files/File");
            if (fileNodes != null && fileNodes.Count > 0)
            {
                foreach (XmlNode fileNode in fileNodes)
                {
                    int docID = int.Parse(fileNode.Attributes.GetNamedItem("DocID").Value);
                    XmlNodeList pageNodes = fileNode.SelectNodes("Page");
                    if (pageNodes != null && pageNodes.Count > 0)
                    {
                        Dictionary<int, Dictionary<string, RectangleF>> pageLayout =
                                new Dictionary<int, Dictionary<string, RectangleF>>();
                        foreach (XmlNode pageNode in pageNodes)
                        {
                            
                            int pageNum = int.Parse(pageNode.Attributes.GetNamedItem("PageNumber").Value);
                            XmlNodeList colHeaderNodes = pageNode.SelectNodes("ColumnHeader");
                            if (colHeaderNodes != null && colHeaderNodes.Count > 0)
                            {
                                Dictionary<string, RectangleF> colHeaderLayout = new Dictionary<string, RectangleF>();
                                foreach (XmlNode colHeaderNode in colHeaderNodes)
                                {
                                    string colHeader = colHeaderNode.Attributes.GetNamedItem("Name").Value;
                                    float x = float.Parse(colHeaderNode.Attributes.GetNamedItem("X").Value);
                                    float y = float.Parse(colHeaderNode.Attributes.GetNamedItem("Y").Value);
                                    float w = float.Parse(colHeaderNode.Attributes.GetNamedItem("W").Value);
                                    float h = float.Parse(colHeaderNode.Attributes.GetNamedItem("H").Value);
                                    colHeaderLayout.Add(colHeader, new RectangleF(x, y, w, h));
                                }
                                pageLayout.Add(pageNum, colHeaderLayout);
                            }
                        }
                        layout.Add(docID, pageLayout);    
                    }
                }
            }
            return layout;
        }

        private Dictionary<int, string> ReadFileList(string fileListXmlFilePath)
        {
            Dictionary<int, string> docIDFilePathMappings = new Dictionary<int, string>();
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load(fileListXmlFilePath);
            XmlNodeList fileNodes = xDoc.SelectNodes("//Files/File");
            if (fileNodes != null && fileNodes.Count > 0)
            {
                foreach (XmlNode fileNode in fileNodes)
                {
                    int docID = int.Parse(fileNode.Attributes.GetNamedItem("DocID").Value);
                    string filePath = fileNode.Attributes.GetNamedItem("FilePath").Value;
                    docIDFilePathMappings.Add(docID, filePath);
                }
            }
            return docIDFilePathMappings;
        }

        private Dictionary<int, PdfScanOutput.PdfScanResult> ReadTableScanResult(string tableScanOutputFilePath)
        {
            Dictionary<int, PdfScanOutput.PdfScanResult> tablePages = new Dictionary<int, PdfScanOutput.PdfScanResult>();
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load(tableScanOutputFilePath);
            XmlNodeList fileNodes = xDoc.SelectNodes("//Files/File");
            if (fileNodes != null && fileNodes.Count > 0)
            {
                foreach (XmlNode fileNode in fileNodes)
                {
                    int docID = int.Parse(fileNode.Attributes.GetNamedItem("DocID").Value);
                    PdfScanOutput.PdfScanResult scanResult = new PdfScanOutput.PdfScanResult();
                    scanResult.IsElectronic = bool.Parse(fileNode.Attributes.GetNamedItem("IsElectronic").Value);
                    scanResult.IsScanned = bool.Parse(fileNode.Attributes.GetNamedItem("IsScanned").Value);
                    scanResult.PageNumbers = IntUtil.ToArray(fileNode.Attributes.GetNamedItem("Pages").Value);
                    tablePages.Add(docID, scanResult);
                }
            }
            return tablePages;
        }
        #endregion

        #region write output
        private void WriteTableDataToOutput(int docID, string fileName, Dictionary<int, DataTable> tableDataByPage)
        {
            int recordCountInFile = 0;
            if (tableDataByPage != null && tableDataByPage.Count > 0)
            {
                foreach (int pageNum in tableDataByPage.Keys)
                {
                    DataTable dtData = tableDataByPage[pageNum];
                    if (dtData != null && dtData.Rows.Count > 0)
                    {
                        string outputFilePath = Path.Combine(this._Instruction.TableExtractorOutputFolder,
                            fileName + "." + pageNum + ".xml");
                        XmlTextWriter outputWriter = new XmlTextWriter(outputFilePath, Encoding.UTF8);
                        outputWriter.WriteStartElement("File");
                        outputWriter.WriteAttributeString("DocID", docID.ToString());
                        outputWriter.WriteAttributeString("PageNumber", pageNum.ToString());
                        outputWriter.WriteStartElement("Columns");
                        foreach (DataColumn col in dtData.Columns)
                        {
                            outputWriter.WriteStartElement("Column");
                            outputWriter.WriteAttributeString("ColumnName", col.ColumnName);
                            outputWriter.WriteEndElement();
                        }
                        outputWriter.WriteEndElement();     // end of columns 

                        outputWriter.WriteStartElement("Rows");
                        foreach (DataRow dr in dtData.Rows)
                        {
                            recordCountInFile++;
                            outputWriter.WriteStartElement("Row");
                            for (int i = 0; i < dtData.Columns.Count; i++)
                            {
                                outputWriter.WriteStartElement("Cell");
                                if (dr[i] != null)
                                {
                                    outputWriter.WriteString(dr[i].ToString());
                                }
                                outputWriter.WriteEndElement();
                            }
                            outputWriter.WriteEndElement(); // end of row
                        }
                        outputWriter.WriteEndElement();     // end of rows 

                        outputWriter.WriteEndElement(); // end of file
                        outputWriter.Flush();
                        outputWriter.Close();

                        if(this._ExtractedResultFiles.ContainsKey(docID))
                        {
                            List<string> xmlFiles = this._ExtractedResultFiles[docID];
                            xmlFiles.Add(outputFilePath);
                            this._ExtractedResultFiles[docID] = xmlFiles;
                        }
                        else
                        {
                            List<string> xmlFiles = new List<string>();
                            xmlFiles.Add(outputFilePath);
                            this._ExtractedResultFiles.Add(docID, xmlFiles);
                        }
                    }
                    
                }
            }
            this._RecordsExtracted += recordCountInFile;
        }
        #endregion

        #region save output to db
        private void SaveToDB(List<int> allDocIDs, 
            Dictionary<int, PdfScanOutput.PdfScanResult> scanResults,
            Dictionary<int, List<string>> extractResultFiles)
        {
            SqlConnection conn = new SqlConnection(this._Instruction.ConnStr);
            List<string> pkFields =
                DbUtil.GetTablePrimaryKeys(this._Instruction.ConnStr, "", this._Instruction.DocStatusOutputTable);
            if(pkFields==null && pkFields.Count==0)
                throw new Exception("Doc status table must have primary key field that is identity");

            string pkFieldName = pkFields[0];
            string fkFieldName = pkFieldName;
            if( DbUtil.GetTableColumn(DataProviderType.MSSQL, this._Instruction.ConnStr, string.Empty,
                                  this._Instruction.DocRecordOutputTable, fkFieldName).ColumnName == null)
            {
                throw new Exception("Doc record table must have a foreign key field with the same as " + pkFieldName);
            }

            Dictionary<string, SqlDbType> tgtCols1 = new Dictionary<string, SqlDbType>();
            Dictionary<string, int> tgtFieldSizes1 = new Dictionary<string, int>();
            tgtCols1.Add(pkFieldName, SqlDbType.Int);
            tgtCols1.Add(this._Instruction.DocIDField, SqlDbType.Int);
            tgtCols1.Add(this._Instruction.RunIDField, SqlDbType.Int);
            tgtCols1.Add(this._Instruction.IsElectronicField, SqlDbType.Bit);
            tgtCols1.Add(this._Instruction.IsScannedField, SqlDbType.Bit);
            tgtCols1.Add(this._Instruction.PagesField, SqlDbType.VarChar);
            tgtCols1.Add(this._Instruction.RecordCountField, SqlDbType.Int);
            tgtCols1.Add(this._Instruction.DocFlagField, SqlDbType.Int);
            tgtCols1.Add(this._Instruction.CommentsField, SqlDbType.VarChar);

            tgtFieldSizes1.Add(pkFieldName, FieldDataType.GetFieldDefaultSize(SqlDbType.Int));
            tgtFieldSizes1.Add(this._Instruction.DocIDField, FieldDataType.GetFieldDefaultSize(SqlDbType.Int));
            tgtFieldSizes1.Add(this._Instruction.RunIDField, FieldDataType.GetFieldDefaultSize(SqlDbType.Int));
            tgtFieldSizes1.Add(this._Instruction.IsElectronicField, FieldDataType.GetFieldDefaultSize(SqlDbType.Bit));
            tgtFieldSizes1.Add(this._Instruction.IsScannedField, FieldDataType.GetFieldDefaultSize(SqlDbType.Bit));
            tgtFieldSizes1.Add(this._Instruction.PagesField, 255);
            tgtFieldSizes1.Add(this._Instruction.RecordCountField, FieldDataType.GetFieldDefaultSize(SqlDbType.Int));
            tgtFieldSizes1.Add(this._Instruction.DocFlagField, FieldDataType.GetFieldDefaultSize(SqlDbType.Int));
            tgtFieldSizes1.Add(this._Instruction.CommentsField, 1000);

            string insertSPName1 = string.Format("{0}_BulkInsert_SP", this._Instruction.DocStatusOutputTable);
            Dictionary<string, SqlParameter> paramColl1 = this.CreateInsertStoredProcedures(
                this._Instruction.DocStatusOutputTable, insertSPName1,
                tgtCols1, tgtFieldSizes1, pkFieldName);

            Dictionary<string, SqlDbType> tgtCols2 = new Dictionary<string, SqlDbType>();
            Dictionary<string, int> tgtFieldSizes2 = new Dictionary<string, int>();
            tgtCols2.Add(fkFieldName, SqlDbType.Int);
            tgtFieldSizes2.Add(fkFieldName, FieldDataType.GetFieldDefaultSize(SqlDbType.Int));
            foreach(string srcFieldName in this._Instruction.SrcTgtFieldMappings.Keys)
            {
                string tgtFieldName = this._Instruction.SrcTgtFieldMappings[srcFieldName];
                tgtCols2.Add(tgtFieldName, SqlDbType.VarChar);
                tgtFieldSizes2.Add(tgtFieldName, 500);
            }
            string insertSpName2 = string.Format("{0}_BulkInsert_SP", this._Instruction.DocRecordOutputTable);
            Dictionary<string, SqlParameter> paramColl2 = this.CreateInsertStoredProcedures(
                this._Instruction.DocRecordOutputTable, insertSpName2, tgtCols2, tgtFieldSizes2, null);

            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;

                int totalFileProcessed = 0;
                int totalRecordExtracted = 0;

                foreach(int docID in allDocIDs)
                {
                    string comment = "Unknown";
                    bool isElectronic = false;
                    bool isScanned = false;
                    DocumentExtractionFlag flag = DocumentExtractionFlag.Unknown;
                    int recordCount = 0;
                    int[] pageNumbers = null;

                    try
                    {
                        if (scanResults.ContainsKey(docID))
                        {
                            PdfScanOutput.PdfScanResult scanResult = scanResults[docID];
                            isElectronic = scanResult.IsElectronic;
                            isScanned = scanResult.IsScanned;
                            pageNumbers = scanResult.PageNumbers;
                            if (pageNumbers != null && pageNumbers.Length > 0)
                            {
                                comment = "Found table";
                                flag = DocumentExtractionFlag.FoundTable;
                            }
                            else
                            {
                                comment = "Unable to detect table";
                                flag = DocumentExtractionFlag.FailedToFindTable;
                            }
                        }

                        Dictionary<int, DataTable> dataByPages = new Dictionary<int, DataTable>();
                        if (extractResultFiles.ContainsKey(docID))
                        {
                            List<string> xmlResultPaths = extractResultFiles[docID];
                            foreach (string xmlFilePath in xmlResultPaths)
                            {
                                XmlDocument xDoc = new XmlDocument();
                                xDoc.Load(xmlFilePath);
                                int pageContainingTable = 0;
                                DataTable dtData = this.GrabExtractionResult(xDoc, ref pageContainingTable);
                                if (dtData != null && dtData.Rows.Count > 0)
                                {
                                    recordCount += dtData.Rows.Count;
                                    dataByPages.Add(pageContainingTable, dtData);
                                }
                            }
                        }
                        if (recordCount > 0)
                        {
                            comment = "Found table data";
                            flag = DocumentExtractionFlag.GotTableData;
                        }

                        cmd.CommandText = insertSPName1;
                        paramColl1[this._Instruction.DocIDField].Value = docID;
                        paramColl1[this._Instruction.RunIDField].Value = this._Instruction.RunID;
                        paramColl1[this._Instruction.IsElectronicField].Value = isElectronic;
                        paramColl1[this._Instruction.IsScannedField].Value = isScanned;
                        paramColl1[this._Instruction.PagesField].Value = IntUtil.ToString(pageNumbers);
                        paramColl1[this._Instruction.RecordCountField].Value = recordCount;
                        paramColl1[this._Instruction.DocFlagField].Value = (int) flag;
                        paramColl1[this._Instruction.CommentsField].Value = comment;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(
                            new SqlParameter[]
                            {
                                paramColl1[pkFieldName],
                                paramColl1[this._Instruction.DocIDField],
                                paramColl1[this._Instruction.RunIDField],
                                paramColl1[this._Instruction.IsElectronicField],
                                paramColl1[this._Instruction.IsScannedField],
                                paramColl1[this._Instruction.PagesField],
                                paramColl1[this._Instruction.RecordCountField],
                                paramColl1[this._Instruction.DocFlagField],
                                paramColl1[this._Instruction.CommentsField]
                            });
                        cmd.ExecuteNonQuery();
                        int pkFieldValue = (int)paramColl1[pkFieldName].Value;

                        cmd.CommandText = insertSpName2;
                        paramColl2[fkFieldName].Value = pkFieldValue;
                        foreach (int pageNum in dataByPages.Keys)
                        {
                            DataTable dtData = dataByPages[pageNum];
                            foreach (DataRow dr in dtData.Rows)
                            {
                                foreach (string srcFieldName in this._Instruction.SrcTgtFieldMappings.Keys)
                                {
                                    string tgtFieldName = this._Instruction.SrcTgtFieldMappings[srcFieldName];
                                    if (dtData.Columns.Contains(srcFieldName) && dr[srcFieldName] != null)
                                    {
                                        paramColl2[tgtFieldName].Value = dr[srcFieldName];
                                    }
                                    else
                                    {
                                        paramColl2[tgtFieldName].Value = DBNull.Value;
                                    }
                                }
                                cmd.Parameters.Clear();
                                foreach (string fieldName in paramColl2.Keys)
                                {
                                    cmd.Parameters.Add(paramColl2[fieldName]);
                                }
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception ex1)
                    {
                        if (this.WorkerErredEvent != null)
                        {
                            this.WorkerErredEvent(this._JobTicketID, this.GetType(), this._Instruction, ex1.ToString(),true);
                        }
                    }

                    totalFileProcessed++;
                    totalRecordExtracted += recordCount;
                    if(this.WorkerStatusChangedEvent !=null && totalFileProcessed % 10==0)
                    {
                        int percent = totalFileProcessed*100/allDocIDs.Count;
                        string msg = string.Format("Processed {0} of {1} files: total of {2} records extracted",
                                                   totalFileProcessed, allDocIDs.Count, totalRecordExtracted);
                        this.WorkerStatusChangedEvent(this._JobTicketID, this.GetType(), this._Instruction, this.WorkflowName, this.ComponentName, msg, percent);
                    }
                }
            }
            catch (Exception ex0)
            {
                if(this.WorkerErredEvent !=null)
                {
                    this.WorkerErredEvent(this._JobTicketID, this.GetType(), this._Instruction, ex0.ToString(),true);
                }
            }
            finally
            {
                conn.Close();
            }
        }

        private Dictionary<string,SqlParameter> CreateInsertStoredProcedures(
            string tgtTableName, 
            string spName,
            Dictionary<string, SqlDbType> tgtColumns,
            Dictionary<string, int> tgtFieldSizes, 
            string identityPKFieldName)
        {
            List<TableColumn> tgtCols = new List<TableColumn>();
            Dictionary<string, SqlParameter> parameters = new Dictionary<string, SqlParameter>();

            if (!string.IsNullOrEmpty(identityPKFieldName))
            {
                TableColumn pkCol = new TableColumn();
                pkCol.ColumnName = identityPKFieldName;
                pkCol.DbType = SqlDbType.Int;
                pkCol.IsPK = true;
                pkCol.IsIdentity = true;
                tgtCols.Add(pkCol);
                SqlParameter pkParam = new SqlParameter("@" + identityPKFieldName, SqlDbType.Int, FieldDataType.GetFieldDefaultSize(SqlDbType.Int));
                pkParam.Direction = ParameterDirection.Output;
                parameters.Add(identityPKFieldName, pkParam);
            }

            foreach(string fieldName in tgtColumns.Keys)
            {
                if(identityPKFieldName==null || fieldName !=identityPKFieldName)
                {
                    TableColumn col = new TableColumn();
                    col.ColumnName = fieldName;
                    col.DbType = tgtColumns[fieldName];
                    if (tgtFieldSizes.ContainsKey(fieldName))
                    {
                        col.Size = tgtFieldSizes[fieldName];
                    }
                    else
                    {
                        col.Size = FieldDataType.GetFieldDefaultSize(col.DbType);
                    }
                    tgtCols.Add(col);

                    SqlParameter sqlParam=new SqlParameter("@"+fieldName, DBNull.Value);
                    parameters.Add(fieldName, sqlParam);
                }
            }

            if(!string.IsNullOrEmpty(identityPKFieldName))
            {
                string spBody = StoredProcUtil.CreateStoredProcedureStatementForIdentityInsert(
                    DataProviderType.MSSQL, spName, string.Empty, tgtTableName,
                    tgtCols.ToArray(), identityPKFieldName);
                DbUtil.NewStoredProc(this._Instruction.ConnStr, spName, spBody);
            }
            else
            {
                string spBody = StoredProcUtil.CreateStoredProcedureStatementForInsert(
                    DataProviderType.MSSQL, spName, string.Empty, tgtTableName, tgtCols.ToArray());
                DbUtil.NewStoredProc(this._Instruction.ConnStr, spName, spBody);
            }
            
            return parameters;
        }

        private DataTable GrabExtractionResult(XmlDocument xDoc, ref int pageNum)
        {
            XmlNode fileNode = xDoc.DocumentElement;
            //docID = int.Parse(fileNode.Attributes.GetNamedItem("DocID").Value);
            pageNum = int.Parse(fileNode.Attributes.GetNamedItem("PageNumber").Value);

            DataTable dtData = new DataTable();
            XmlNodeList colNodes = fileNode.SelectNodes("Columns/Column");
            foreach(XmlNode colNode in colNodes)
            {
                string colName = colNode.Attributes.GetNamedItem("ColumnName").Value;
                dtData.Columns.Add(colName, typeof (string));
            }

            XmlNodeList rowNodes = fileNode.SelectNodes("Rows/Row");
            foreach(XmlNode rowNode in rowNodes)
            {
                DataRow dr = dtData.NewRow();
                for(int i=0;i<rowNode.ChildNodes.Count;i++)
                {
                    XmlNode cellNode = rowNode.ChildNodes[i];
                    if(cellNode.InnerText !=null)
                    {
                        dr[i] = cellNode.InnerText;
                    }
                    else
                    {
                        dr[i] = string.Empty;
                    }
                }
                dtData.Rows.Add(dr);
            }
            return dtData;
        }
        #endregion

    }
}
